"""
账单相关的数据库操作函数
"""

from .db import get_db_connection, close_connection

async def get_bills(user_id, bill_type=None, type=None, payment_status=None, payment_method=None):
    """
    获取用户的账单列表
    
    Args:
        user_id: 用户ID
        bill_type: 账单类型（可选）
        type: 支付类型（可选）
        payment_status: 支付状态（可选）
        payment_method: 支付方式（可选）
    
    Returns:
        list: 账单列表
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                SELECT * FROM bills 
                WHERE user_id = %s
            """
            params = [user_id]
            
            if bill_type:
                sql += " AND bill_type LIKE %s"
                params.extend([f"%{bill_type}%"])

            if type is not None:
                sql += " AND type = %s"
                params.append(type)

            if payment_status is not None:
                sql += " AND payment_status = %s"
                params.append(payment_status)

            if payment_method:
                sql += " AND payment_method = %s"
                params.append(payment_method)
                
            await cursor.execute(sql, params)
            bills = await cursor.fetchall()
            return bills

    except Exception as e:
        print(f"获取账单列表失败: {str(e)}")
        return []
    finally:
        await close_connection(connection)

async def add_bill(user_id, bill_type, amount, type, payment_status, payment_method, due_date, description):
    """
    添加新账单
    
    Args:
        user_id: 用户ID
        bill_type: 账单类型
        amount: 金额
        type: 支付类型
        payment_status: 支付状态
        payment_method: 支付方式
        due_date: 到期日期
        description: 描述
    
    Returns:
        bool: 是否添加成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                INSERT INTO bills (
                    user_id, bill_type, amount, type, payment_status, payment_method, due_date, description
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s
                )
            """
            params = [user_id, bill_type, amount, type, payment_status, payment_method, due_date, description]
            
            await cursor.execute(sql, params)
            await connection.commit()
            return True

    except Exception as e:
        print(f"添加账单失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def delete_bill(user_id, bill_id):
    """
    删除账单
    
    Args:
        user_id: 用户ID
        bill_id: 账单ID
    
    Returns:
        bool: 是否删除成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                DELETE FROM bills 
                WHERE user_id = %s AND id = %s
            """
            params = [user_id, bill_id]
            await cursor.execute(sql, params)
            await connection.commit()
            return True
    except Exception as e:
        print(f"删除账单失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def update_bill(user_id, bill_id, bill_type, amount, type, payment_status, payment_method, due_date, description):
    """
    更新账单信息
    
    Args:
        user_id: 用户ID
        bill_id: 账单ID
        bill_type: 账单类型
        amount: 金额
        type: 支付类型
        payment_status: 支付状态
        payment_method: 支付方式
        due_date: 到期日期
        description: 描述
    
    Returns:
        bool: 是否更新成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                UPDATE bills 
                SET bill_type = %s, amount = %s, type = %s, payment_status = %s, payment_method = %s, due_date = %s, description = %s
                WHERE user_id = %s AND id = %s
            """
            params = [bill_type, amount, type, payment_status, payment_method, due_date, description, user_id, bill_id]
            
            await cursor.execute(sql, params)
            await connection.commit()
            return True

    except Exception as e:
        print(f"更新账单失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)